High Volume Index Scans

If your high volume SQL is using an index, it may be a problem. There are 6 main types of index scan; in Explain Plan you will see one of the following:

[UNIQUE] INDEX RANGE SCAN

Oracle is reading 0 or more contiguous rows from the index.

[UNIQUE] INDEX RANGE SCAN (MIN/MAX)

Oracle is identifying 0 or more contiguous rows in the index, but is reading only one (the first or the last) in order to satisy a MIN or MAX aggregate function.

UNIQUE INDEX UNIQUE SCAN

Oracle is reading 0 or 1 rows from the index.

[UNIQUE] INDEX FULL SCAN

Oracle is reading all rows from the index, and may be accessing these rows in the underlying table.

[UNIQUE] INDEX SKIP SCAN

Oracle is reading 0 or more rows from different parts of the index, and may be accessing these rows in the underlying table.

[UNIQUE] INDEX FAST FULL SCAN

Oracle is reading all rows from the index, and is not accessing these rows in the underlying table. ie. The index contains all columns required to resolve the query without having to lookup the table.

In a high volume SQL, the last index scan type above is not a problem. With the first five types, they are usually only a problem if Oracle is also performing a table access along with the index scan. The table access will appear in your Explain Plan immediately above the index scan. eg:

TABLE ACCESS BY INDEX ROWID table_name
    UNIQUE INDEX RANGE SCAN index_name

If your SQL is not performing a table access, but the index uses a RANGE SCAN (but not a RANGE SCAN MIN/MAX), you could still have a Range Scan problem, where the range scan is processing a lot more rows than you expect. Otherwise, an index scan that does nore have a TABLE ACCESS is not necessarily a problem; try performing a full table scan instead and compare the performance.

• If you have an INDEX() hint, remove it.
• If you have an RULE hint, remove it.
• Add a FULL hint to force a full table scan.

Does the full table scan perform better? If not, then one of the following will apply:

• You may have a Low Volume SQL, or a combination Low and High volume SQL.
  Run the original SQL through TK*Prof with EXPLAIN=uid/pwd and check how many rows are selected from the index. If the TK*Prof row counts show zeros, make sure you exited your session before running TK*Prof, or check with the DBA in case that feature has been disabled on your database. If the rows selected from the index are less than say 4% of the total rows in the table or partition, then Oracle is probably right to use the index.
• One of the other High Volume SQL problems apply in addition to the index problem. Leave the SQL performing a Full Table Scan and try one of the other fixes.